MIRR (values, financeRate, reinvestRate)

Basic and Crystal syntax.

Arguments

• values is a Number or Currency type array that specifies cash flow values. The array must contain at least one negative value (a payment) and one positive value (a receipt). The cash flows must occur at regular intervals such as monthly or yearly.
• financeRate is a Number that specifies the interest rate paid as the cost of financing.
• reinvestRate is a Number that specifies the interest rate received on gains from cash reinvestment.

Returns

Number value

Action

MIRR returns a Number specifying the modified internal rate of return for a series of periodic cash flows (payments and receipts).

Example

The following example is applicable to both Basic and Crystal syntax:

Suppose that you run a business that makes equipment investments, which result in a loss in your first and fourth years. Your expected annual returns are: -$50,000, $40,000, $65,000, -$60,000, $50,000, $70,000. Your losses are financed at 10 percent while you reinvest your earnings in an account at 6 percent. The modified internal rate of return is:

Rem Basic syntax
formula = MIRR(Array(-50000, 40000, 65000, -60000, 50000, 70000), 0.10, 0.06)

//Crystal syntax
MIRR([-50000, 40000, 65000, -60000, 50000, 70000], 0.10, 0.06)

Returns 0.214 (rounded to 3 decimals) or 21.4 percent.

Comment

This function is designed to work like the Visual Basic function of the same name.